﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Maticsoft.DBUtility;
using System.Data.SqlClient;
using System.Data;

namespace CJJCH.DAL
{
    public class T_PRD_Book
    {

        public T_PRD_Book()
        { }
        #region  成员方法

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(Guid ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from T_PRD_Book");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.UniqueIdentifier)};
            parameters[0].Value = ID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(CJJCH.Model.T_PRD_Book model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into T_PRD_Book(");
            strSql.Append("ID,BookTypeID,BookStateID,BookName,BookImage,BookFile,BookSize,BookPage,BookAuthor,BookTrans,PublishCom,PubishDate,RunDate,BookISBN,BookPrice,BookDirectory,BookNote,Hits,IsHot,IsValid,ManagerID)");
            strSql.Append(" values (");
            strSql.Append("@ID,@BookTypeID,@BookStateID,@BookName,@BookImage,@BookFile,@BookSize,@BookPage,@BookAuthor,@BookTrans,@PublishCom,@PubishDate,@RunDate,@BookISBN,@BookPrice,@BookDirectory,@BookNote,@Hits,@IsHot,@IsValid,@ManagerID)");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@BookTypeID", SqlDbType.Int,4),
					new SqlParameter("@BookStateID", SqlDbType.Int,4),
					new SqlParameter("@BookName", SqlDbType.NVarChar,200),
					new SqlParameter("@BookImage", SqlDbType.NVarChar,100),
					new SqlParameter("@BookFile", SqlDbType.NVarChar,100),
					new SqlParameter("@BookSize", SqlDbType.NVarChar,50),
					new SqlParameter("@BookPage", SqlDbType.NVarChar,50),
					new SqlParameter("@BookAuthor", SqlDbType.NVarChar,200),
					new SqlParameter("@BookTrans", SqlDbType.NVarChar,200),
					new SqlParameter("@PublishCom", SqlDbType.NVarChar,200),
					new SqlParameter("@PubishDate", SqlDbType.DateTime),
					new SqlParameter("@RunDate", SqlDbType.DateTime),
					new SqlParameter("@BookISBN", SqlDbType.NVarChar,50),
					new SqlParameter("@BookPrice", SqlDbType.Decimal,9),
					new SqlParameter("@BookDirectory", SqlDbType.NText),
					new SqlParameter("@BookNote", SqlDbType.NText),
					new SqlParameter("@Hits", SqlDbType.Int,4),
					new SqlParameter("@IsHot", SqlDbType.Bit,1),
					new SqlParameter("@IsValid", SqlDbType.Bit,1),
					new SqlParameter("@ManagerID", SqlDbType.UniqueIdentifier,16)};
            parameters[0].Value = model.ID;
            parameters[1].Value = model.BookTypeID;
            parameters[2].Value = model.BookStateID;
            parameters[3].Value = model.BookName;
            parameters[4].Value = model.BookImage;
            parameters[5].Value = model.BookFile;
            parameters[6].Value = model.BookSize;
            parameters[7].Value = model.BookPage;
            parameters[8].Value = model.BookAuthor;
            parameters[9].Value = model.BookTrans;
            parameters[10].Value = model.PublishCom;
            parameters[11].Value = model.PubishDate;
            parameters[12].Value = model.RunDate;
            parameters[13].Value = model.BookISBN;
            parameters[14].Value = model.BookPrice;
            parameters[15].Value = model.BookDirectory;
            parameters[16].Value = model.BookNote;
            parameters[17].Value = model.Hits;
            parameters[18].Value = model.IsHot;
            parameters[19].Value = model.IsValid;
            parameters[20].Value = model.ManagerID;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public void Update(CJJCH.Model.T_PRD_Book model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update T_PRD_Book set ");
            strSql.Append("BookTypeID=@BookTypeID,");
            strSql.Append("BookStateID=@BookStateID,");
            strSql.Append("BookName=@BookName,");
            strSql.Append("BookImage=@BookImage,");
            strSql.Append("BookFile=@BookFile,");
            strSql.Append("BookSize=@BookSize,");
            strSql.Append("BookPage=@BookPage,");
            strSql.Append("BookAuthor=@BookAuthor,");
            strSql.Append("BookTrans=@BookTrans,");
            strSql.Append("PublishCom=@PublishCom,");
            strSql.Append("PubishDate=@PubishDate,");
            strSql.Append("RunDate=@RunDate,");
            strSql.Append("BookISBN=@BookISBN,");
            strSql.Append("BookPrice=@BookPrice,");
            strSql.Append("BookDirectory=@BookDirectory,");
            strSql.Append("BookNote=@BookNote,");
            strSql.Append("Hits=@Hits,");
            strSql.Append("IsHot=@IsHot,");
            strSql.Append("IsValid=@IsValid,");
            strSql.Append("ManagerID=@ManagerID,");
            strSql.Append("BuildDate=@BuildDate");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@BookTypeID", SqlDbType.Int,4),
					new SqlParameter("@BookStateID", SqlDbType.Int,4),
					new SqlParameter("@BookName", SqlDbType.NVarChar,200),
					new SqlParameter("@BookImage", SqlDbType.NVarChar,100),
					new SqlParameter("@BookFile", SqlDbType.NVarChar,100),
					new SqlParameter("@BookSize", SqlDbType.NVarChar,50),
					new SqlParameter("@BookPage", SqlDbType.NVarChar,50),
					new SqlParameter("@BookAuthor", SqlDbType.NVarChar,200),
					new SqlParameter("@BookTrans", SqlDbType.NVarChar,200),
					new SqlParameter("@PublishCom", SqlDbType.NVarChar,200),
					new SqlParameter("@PubishDate", SqlDbType.DateTime),
					new SqlParameter("@RunDate", SqlDbType.DateTime),
					new SqlParameter("@BookISBN", SqlDbType.NVarChar,50),
					new SqlParameter("@BookPrice", SqlDbType.Decimal,9),
					new SqlParameter("@BookDirectory", SqlDbType.NText),
					new SqlParameter("@BookNote", SqlDbType.NText),
					new SqlParameter("@Hits", SqlDbType.Int,4),
					new SqlParameter("@IsHot", SqlDbType.Bit,1),
					new SqlParameter("@IsValid", SqlDbType.Bit,1),
					new SqlParameter("@ManagerID", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@BuildDate", SqlDbType.DateTime)};
            parameters[0].Value = model.ID;
            parameters[1].Value = model.BookTypeID;
            parameters[2].Value = model.BookStateID;
            parameters[3].Value = model.BookName;
            parameters[4].Value = model.BookImage;
            parameters[5].Value = model.BookFile;
            parameters[6].Value = model.BookSize;
            parameters[7].Value = model.BookPage;
            parameters[8].Value = model.BookAuthor;
            parameters[9].Value = model.BookTrans;
            parameters[10].Value = model.PublishCom;
            parameters[11].Value = model.PubishDate;
            parameters[12].Value = model.RunDate;
            parameters[13].Value = model.BookISBN;
            parameters[14].Value = model.BookPrice;
            parameters[15].Value = model.BookDirectory;
            parameters[16].Value = model.BookNote;
            parameters[17].Value = model.Hits;
            parameters[18].Value = model.IsHot;
            parameters[19].Value = model.IsValid;
            parameters[20].Value = model.ManagerID;
            parameters[21].Value = model.BuildDate;

            DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public void Delete(Guid ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from T_PRD_Book ");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.UniqueIdentifier)};
            parameters[0].Value = ID;

            DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public CJJCH.Model.T_PRD_Book GetModel(Guid ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select  top 1 B.*,case when N.TypeName is not null then N.typeName+'->'+T.TypeName else T.TypeName end as TypeName");
            strSql.Append(" from T_PRD_Book B  left JOIN  ");
            strSql.Append(" ((select * from T_PRD_BookType where ID not in  (select distinct PID from T_PRD_BookType)) ");
            strSql.Append(" T left join  (select * from T_PRD_BookType where ID  in (select distinct PID from T_PRD_BookType)) N ");
            strSql.Append("  on n.ID= T.PID) ON B.BookTypeID=T.ID where B.ID=@ID ");
            strSql.Append(" ORDER BY T.PID,T.OrderNum ASC");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.UniqueIdentifier)};
            parameters[0].Value = ID;

            CJJCH.Model.T_PRD_Book model = new CJJCH.Model.T_PRD_Book();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    model.ID = new Guid(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["BookTypeID"].ToString() != "")
                {
                    model.BookTypeID = int.Parse(ds.Tables[0].Rows[0]["BookTypeID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["BookStateID"].ToString() != "")
                {
                    model.BookStateID = int.Parse(ds.Tables[0].Rows[0]["BookStateID"].ToString());
                }
                model.BookName = ds.Tables[0].Rows[0]["BookName"].ToString();
                model.BookImage = ds.Tables[0].Rows[0]["BookImage"].ToString();
                model.BookFile = ds.Tables[0].Rows[0]["BookFile"].ToString();
                model.BookSize = ds.Tables[0].Rows[0]["BookSize"].ToString();
                model.BookPage = ds.Tables[0].Rows[0]["BookPage"].ToString();
                model.BookAuthor = ds.Tables[0].Rows[0]["BookAuthor"].ToString();
                model.BookTrans = ds.Tables[0].Rows[0]["BookTrans"].ToString();
                model.PublishCom = ds.Tables[0].Rows[0]["PublishCom"].ToString();
                if (ds.Tables[0].Rows[0]["PubishDate"].ToString() != "")
                {
                    model.PubishDate = DateTime.Parse(ds.Tables[0].Rows[0]["PubishDate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["RunDate"].ToString() != "")
                {
                    model.RunDate = DateTime.Parse(ds.Tables[0].Rows[0]["RunDate"].ToString());
                }
                model.BookISBN = ds.Tables[0].Rows[0]["BookISBN"].ToString();
                if (ds.Tables[0].Rows[0]["BookPrice"].ToString() != "")
                {
                    model.BookPrice = decimal.Parse(ds.Tables[0].Rows[0]["BookPrice"].ToString());
                }
                model.BookDirectory = ds.Tables[0].Rows[0]["BookDirectory"].ToString();
                model.BookNote = ds.Tables[0].Rows[0]["BookNote"].ToString();
                if (ds.Tables[0].Rows[0]["Hits"].ToString() != "")
                {
                    model.Hits = int.Parse(ds.Tables[0].Rows[0]["Hits"].ToString());
                }
               

                if (ds.Tables[0].Rows[0]["IsHot"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["IsHot"].ToString() == "1") || (ds.Tables[0].Rows[0]["IsHot"].ToString().ToLower() == "true"))
                    {
                        model.IsHot = true;
                    }
                    else
                    {
                        model.IsHot = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["IsValid"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["IsValid"].ToString() == "1") || (ds.Tables[0].Rows[0]["IsValid"].ToString().ToLower() == "true"))
                    {
                        model.IsValid = true;
                    }
                    else
                    {
                        model.IsValid = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["ManagerID"].ToString() != "")
                {
                    model.ManagerID = new Guid(ds.Tables[0].Rows[0]["ManagerID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["BuildDate"].ToString() != "")
                {
                    model.BuildDate = DateTime.Parse(ds.Tables[0].Rows[0]["BuildDate"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,BookTypeID,BookStateID,BookName,BookImage,BookFile,BookSize,BookPage,BookAuthor,BookTrans,PublishCom,PubishDate,RunDate,BookISBN,BookPrice,BookDirectory,BookNote,Hits,IsHot,IsValid,ManagerID,BuildDate ");
            strSql.Append(" FROM T_PRD_Book ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" ID,BookTypeID,BookStateID,BookName,BookImage,BookFile,BookSize,BookPage,BookAuthor,BookTrans,PublishCom,PubishDate,RunDate,BookISBN,BookPrice,BookDirectory,BookNote,Hits,IsHot,IsValid,ManagerID,BuildDate ");
            strSql.Append(" FROM T_PRD_Book ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = "T_PRD_Book";
            parameters[1].Value = "ID";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/

        #endregion  成员方法
        /// <summary>
        /// 头部最新图书及首页
        /// </summary>
        /// <returns></returns>
        public DataSet GetBookTop(int top)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append(" SELECT top (@top) ID, BookName,BookImage,BookPrice  FROM T_PRD_Book");
            strSQL.Append(" where IsValid=1  and BookStateID=1 order by  PubishDate desc ");
            SqlParameter[] parameters = {
					new SqlParameter("@top", SqlDbType.Int,4)};
            parameters[0].Value = top;
            DataSet ds = new DataSet();
            ds = DbHelperSQL.Query(strSQL.ToString(), parameters);
            return ds;
        }

    }
}
